CREATE DATABASE RadioCabs
GO
USE RadioCabs
GO
CREATE TABLE ShipType
(
	ShipTypeID		INT IDENTITY(1,1)	PRIMARY KEY,
	ShipTypeName	VARCHAR(50)			NOT NULL,
	CHECK (ShipTypeName IN ('Premium','Basic','Free'))
)
GO
CREATE TABLE PaymentType
(
	PaymentTypeID		INT IDENTITY(1,1)	PRIMARY KEY,
	PaymentTypeName		VARCHAR(50)			NOT NULL,
	PaymentBy			VARCHAR(30), -- Monthly, Quarterly
	Cost				AS CASE
							WHEN (PaymentTypeName = 'Registration' OR PaymentTypeName = 'Advertisement') 
										AND (PaymentBy = 'Monthly')	THEN	15
							WHEN (PaymentTypeName = 'Registration' OR PaymentTypeName = 'Advertisement') 
										AND (PaymentBy = 'Quarterly')	THEN	45
							WHEN (PaymentTypeName = 'Driver' AND PaymentBy = 'Monthly')	THEN	10
							WHEN (PaymentTypeName = 'Driver' AND PaymentBy = 'Quarterly')	THEN	25
							ELSE -1
						END,
	CHECK (PaymentTypeName IN ('Registration','Driver','Advertisement')),
	CHECK (PaymentBy IN ('Monthly','Quarterly'))
)
GO
CREATE TABLE UserRole
(
	RoleID		INT	IDENTITY(1,1)	PRIMARY KEY,
	RoleName	VARCHAR(30)			NOT NULL,
	CHECK (RoleName IN ('Listing','Driver','Advertise'))
)
GO
CREATE TABLE UserRadioCab
(
	URCID	INT IDENTITY(1,1)		PRIMARY KEY,
	RoleID	INT FOREIGN KEY REFERENCES	UserRole(RoleID)	NULL,
	ShipTypeID	INT FOREIGN KEY REFERENCES	ShipType(ShipTypeID)	NULL,
	URCName	VARCHAR(100)			UNIQUE	NULL,
	URCPassword	VARCHAR(20)			NULL,
	URCContactPerson	VARCHAR(50)	NULL,
	URCDesignation	VARCHAR(50)		NULL,
	URCAddress	VARCHAR(100)		NULL,
	URCCity	VARCHAR(30)				NULL,
	URCMobile	VARCHAR(25)			NULL,
	URCTelephone	VARCHAR(25)		NULL,
	URCFaxNumber	VARCHAR(25)		NULL,
	URCEmail	VARCHAR(50)			NULL,
	URCExperience	VARCHAR(10)		NULL,
	URCDescription	VARCHAR(100)	NULL,
	PaymentTypeID	INT FOREIGN KEY REFERENCES PaymentType(PaymentTypeID)	NULL,
	URCStatus BIT NULL
)
GO
CREATE TABLE PaymentDetails
(
	PaymentDetailID	INT IDENTITY(1,1) PRIMARY KEY,
	URCID	INT FOREIGN KEY REFERENCES UserRadioCab(URCID)	NULL,
	PDStartDate	DATETIME NULL,
	PDEndDate	DATETIME NULL,
	PDMoney		INT	NULL ,
	PDStatus	BIT NULL 
)
GO
CREATE TABLE Feedback
(
	FeedbackID INT IDENTITY(1,1) primary key not null,
	FeedbackName nvarchar(max) null,
	FeedbackEmail nvarchar(max) null,
	FeedbackDescription nvarchar(max) null,
	FeedbackCity nvarchar(100) null,
	FeedbackType nvarchar(100) null,
	CHECK (FeedbackType IN ('Complaint','Compliment','Suggestion'))
)